﻿

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using MySql.Data.MySqlClient;

public class DAL_tbl_rm_receipt
{
    public DAL_tbl_rm_receipt()
    {

    }



    public List<tbl_rm_receipt> select(int ID)
    {
        List<tbl_rm_receipt> list = new List<tbl_rm_receipt>();
        MySqlCommand cmd = new MySqlCommand();
        cmd.CommandText = "Select ID,RM_Order_Number,Quantity_Received,Date,Note,Bag_Number,Drum_Number,IS_ACTIVE,CREATED_BY,CREATED_DATE,UPDATED_BY,UPDATED_DATE from tbl_rm_receipt where ID=@ID";
        cmd.Parameters.AddWithValue("@ID", ID);
        DataTable dt = DBUtil.ExecuteDataTable(cmd);
        foreach (DataRow dr in dt.Rows)
        {
            list.Add(new tbl_rm_receipt()
            {
                ID = Convert.ToInt32(dr["ID"]),
                RM_Order_Number = dr["RM_Order_Number"].ToString(),
                Quantity_Received = Convert.ToDecimal(dr["Quantity_Received"]),
                Date = dr["Date"].toDateTimeWithoutError(),
                Note = dr["Note"].ToString(),
                Bag_Number = dr["Bag_Number"].ToString(),
                Drum_Number = dr["Drum_Number"].ToString(),
                IS_ACTIVE = Convert.ToBoolean(dr["IS_ACTIVE"]),
                CREATED_BY = dr["CREATED_BY"].ToString(),
                CREATED_DATE = dr["CREATED_DATE"].toDateTimeWithoutError(),
                UPDATED_BY = dr["UPDATED_BY"].ToString(),
                UPDATED_DATE = dr["UPDATED_DATE"].toDateTimeWithoutError()
            });
        }

        return list;
    }



    public List<tbl_rm_receipt> selectAll()
    {
        List<tbl_rm_receipt> list = new List<tbl_rm_receipt>();
        MySqlCommand cmd = new MySqlCommand();
        cmd.CommandText = "Select ID,RM_Order_Number,Quantity_Received,Date,Note,Bag_Number,Drum_Number,IS_ACTIVE,CREATED_BY,CREATED_DATE,UPDATED_BY,UPDATED_DATE from tbl_rm_receipt Order By ID";

        DataTable dt = DBUtil.ExecuteDataTable(cmd);
        foreach (DataRow dr in dt.Rows)
        {
            list.Add(new tbl_rm_receipt()
            {
                ID = Convert.ToInt32(dr["ID"]),
                RM_Order_Number = dr["RM_Order_Number"].ToString(),
                Quantity_Received = Convert.ToDecimal(dr["Quantity_Received"]),
                Date = dr["Date"].toDateTimeWithoutError(),
                Note = dr["Note"].ToString(),
                Bag_Number = dr["Bag_Number"].ToString(),
                Drum_Number = dr["Drum_Number"].ToString(),
                IS_ACTIVE = Convert.ToBoolean(dr["IS_ACTIVE"]),
                CREATED_BY = dr["CREATED_BY"].ToString(),
                CREATED_DATE = dr["CREATED_DATE"].toDateTimeWithoutError(),
                UPDATED_BY = dr["UPDATED_BY"].ToString(),
                UPDATED_DATE = dr["UPDATED_DATE"].toDateTimeWithoutError()
            });
        }

        return list;
    }


    public DataTable selectAllForGrid()
    {
        MySqlCommand cmd = new MySqlCommand();
        cmd.CommandText = @"Select ID,RM_Order_Number,Quantity_Received,Date as Date,Note,Bag_Number,Drum_Number,CASE WHEN IS_ACTIVE =1 THEN 'True' ELSE 'False' END as 'IS_ACTIVE',CREATED_BY,CREATED_DATE as CREATED_DATE,UPDATED_BY,UPDATED_DATE as UPDATED_DATE from tbl_rm_receipt Order By ID desc";

        DataTable dt = DBUtil.ExecuteDataTable(cmd);
        return dt;
    }

    public List<tbl_rm_receipt> getRM_Order_Number()
    {
        List<tbl_rm_receipt> list = new List<tbl_rm_receipt>();
        MySqlCommand cmd = new MySqlCommand();
        cmd.CommandText = "Select distinct RM_Order_Number from tbl_rm_receipt Order By ID";

        DataTable dt = DBUtil.ExecuteDataTable(cmd);
        foreach (DataRow dr in dt.Rows)
        {
            list.Add(new tbl_rm_receipt()
            {
                RM_Order_Number = dr["RM_Order_Number"].ToString()
            });
        }

        return list;
    }



    public int insert(String RM_Order_Number, Decimal Quantity_Received, DateTime Date, String Note, String Bag_Number, String Drum_Number, String CREATED_BY, DateTime CREATED_DATE)
    {
        int i = 0;
        string sql = @"Insert into tbl_rm_receipt(RM_Order_Number, Quantity_Received, Date, Note, Bag_Number, Drum_Number, CREATED_BY, CREATED_DATE)
                        values(@RM_Order_Number, @Quantity_Received, @Date, @Note, @Bag_Number, @Drum_Number, @CREATED_BY, @CREATED_DATE)";

        MySqlCommand cmd = new MySqlCommand(sql);

        cmd.Parameters.AddWithValue("@RM_Order_Number", RM_Order_Number);
        cmd.Parameters.AddWithValue("@Quantity_Received", Quantity_Received);
        cmd.Parameters.AddWithValue("@Date", Date);
        cmd.Parameters.AddWithValue("@Note", Note);
        cmd.Parameters.AddWithValue("@Bag_Number", Bag_Number);
        cmd.Parameters.AddWithValue("@Drum_Number", Drum_Number);
        cmd.Parameters.AddWithValue("@CREATED_BY", CREATED_BY);
        cmd.Parameters.AddWithValue("@CREATED_DATE", CREATED_DATE);

        i = DBUtil.ExecuteNonQuery(cmd);
        return i;
    }



    public int update(int ID, String RM_Order_Number, Decimal Quantity_Received, DateTime Date, String Note, String Bag_Number, String Drum_Number, String UPDATED_BY, DateTime UPDATED_DATE)
    {
        int i = 0;
        string sql = @"Update tbl_rm_receipt SET RM_Order_Number=@RM_Order_Number, Quantity_Received=@Quantity_Received, Date=@Date, Note=@Note, Bag_Number=@Bag_Number, Drum_Number=@Drum_Number, UPDATED_BY=@UPDATED_BY, UPDATED_DATE=@UPDATED_DATE WHERE ID=@ID";

        MySqlCommand cmd = new MySqlCommand(sql);

        cmd.Parameters.AddWithValue("@ID", ID);
        cmd.Parameters.AddWithValue("@RM_Order_Number", RM_Order_Number);
        cmd.Parameters.AddWithValue("@Quantity_Received", Quantity_Received);
        cmd.Parameters.AddWithValue("@Date", Date);
        cmd.Parameters.AddWithValue("@Note", Note);
        cmd.Parameters.AddWithValue("@Bag_Number", Bag_Number);
        cmd.Parameters.AddWithValue("@Drum_Number", Drum_Number);
        cmd.Parameters.AddWithValue("@UPDATED_BY", UPDATED_BY);
        cmd.Parameters.AddWithValue("@UPDATED_DATE", UPDATED_DATE);

        i = DBUtil.ExecuteNonQuery(cmd);
        return i;
    }



    public int delete(int ID)
    {
        int i = 0;
        string sql = "Delete FROM tbl_rm_receipt WHERE ID=@ID";

        MySqlCommand cmd = new MySqlCommand(sql);

        cmd.Parameters.AddWithValue("@ID", ID);

        i = DBUtil.ExecuteNonQuery(cmd);
        return i;
    }

    public DataTable getBagAndDrumLastNum( string rmnumber)
    {


        
        MySqlCommand cmd = new MySqlCommand();
        cmd.CommandText = "select * from tbl_rm_receipt where RM_Order_Number=@RM_Order_Number and IS_ACTIVE='1' order by Bag_Number desc, Drum_Number desc";
        cmd.Parameters.AddWithValue("@RM_Order_Number", rmnumber);

        try
        {
            return DBUtil.ExecuteDataTable(cmd);
        }
        finally
        {

            cmd = null;
        }
    }

    public List<tbl_rm_receipt> getBagNumForDll(string RMONumber)
    {
        List<tbl_rm_receipt> list = new List<tbl_rm_receipt>();
        MySqlCommand cmd = new MySqlCommand();
        cmd.CommandText = "Select ID,RM_Order_Number,Quantity_Received,Date,Note,Bag_Number,Drum_Number,IS_ACTIVE,CREATED_BY,CREATED_DATE,UPDATED_BY,UPDATED_DATE from tbl_rm_receipt where RM_Order_Number=@RM_Order_Number order by Bag_Number";
        cmd.Parameters.AddWithValue("@RM_Order_Number", RMONumber);
        DataTable dt = DBUtil.ExecuteDataTable(cmd);
        foreach (DataRow dr in dt.Rows)
        {
            list.Add(new tbl_rm_receipt()
            {
                ID = Convert.ToInt32(dr["ID"]),
                RM_Order_Number = dr["RM_Order_Number"].ToString(),
                Quantity_Received = Convert.ToDecimal(dr["Quantity_Received"]),
                Date = dr["Date"].toDateTimeWithoutError(),
                Note = dr["Note"].ToString(),
                Bag_Number = dr["Bag_Number"].ToString(),
                Drum_Number = dr["Drum_Number"].ToString(),
                IS_ACTIVE = Convert.ToBoolean(dr["IS_ACTIVE"]),
                CREATED_BY = dr["CREATED_BY"].ToString(),
                CREATED_DATE = dr["CREATED_DATE"].toDateTimeWithoutError(),
                UPDATED_BY = dr["UPDATED_BY"].ToString(),
                UPDATED_DATE = dr["UPDATED_DATE"].toDateTimeWithoutError()
            });
        }

        return list;
    }


    public List<tbl_rm_receipt> ddlDrumNumber(string RMONumber)
    {
        List<tbl_rm_receipt> list = new List<tbl_rm_receipt>();
        MySqlCommand cmd = new MySqlCommand();
        cmd.CommandText = "Select distinct Drum_Number from tbl_rm_receipt where RM_Order_Number=@RM_Order_Number and Drum_Number<>'' order by ID desc";
        cmd.Parameters.AddWithValue("@RM_Order_Number", RMONumber);
        DataTable dt = DBUtil.ExecuteDataTable(cmd);
        foreach (DataRow dr in dt.Rows)
        {
            list.Add(new tbl_rm_receipt()
            {
                Drum_Number = dr["Drum_Number"].ToString()
            });
        }

        return list;
    }


    public List<tbl_rm_receipt> ddlBagNumber(string RMONumber)
    {
        List<tbl_rm_receipt> list = new List<tbl_rm_receipt>();
        MySqlCommand cmd = new MySqlCommand();
        cmd.CommandText = "Select distinct Bag_Number from tbl_rm_receipt where RM_Order_Number=@RM_Order_Number and Bag_Number<>'' order by ID desc";
        cmd.Parameters.AddWithValue("@RM_Order_Number", RMONumber);
        DataTable dt = DBUtil.ExecuteDataTable(cmd);
        foreach (DataRow dr in dt.Rows)
        {
            list.Add(new tbl_rm_receipt()
            {
                Bag_Number = dr["Bag_Number"].ToString()
            });
        }

        return list;
    }

   
}